package com.elitel.common.metadata;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 数据库元数据工具类
 *
 * @author baijf
 * @since 2023-10-7 11:17:40
 */
public class DatabaseMetaDataHelper {

    /**
     * MySQL 数据库标识
     */
    private final static String MYSQL_DIALECT = "MySQL";

    /**
     * 获取所有表名
     *
     * @param connection java.sql.Connection
     * @param schema     模式
     * @param types      TableTypeEnum  String => table type. Typical types are "TABLE", "VIEW",
     *                   "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM"
     */
    public static List<MetaTable> getTables(Connection connection, String schema, String[] types) throws SQLException {
        List<MetaTable> resultList = new ArrayList<>();
        DatabaseMetaData meteData = connection.getMetaData();
        ResultSet rs;
        String databaseProductName = meteData.getDatabaseProductName();
        if (MYSQL_DIALECT.equals(databaseProductName)) {
            rs = meteData.getTables(schema, null, null, types);
        } else {
            rs = meteData.getTables(null, schema, null, types);
        }
        while (rs.next()) {
            MetaTable metaTable = new MetaTable();
            metaTable.setTableCat(rs.getString("TABLE_CAT"));
            metaTable.setTableSchema(MYSQL_DIALECT.equals(meteData.getDatabaseProductName()) ?
                    rs.getString("TABLE_CAT") : rs.getString("TABLE_SCHEM"));
            metaTable.setTableName(rs.getString("TABLE_NAME"));
            metaTable.setTableType(rs.getString("TABLE_TYPE"));
            metaTable.setRemarks(rs.getString("REMARKS"));
            resultList.add(metaTable);
        }
        return resultList;
    }

    /**
     * 获取所有列名
     *
     * @param connection java.sql.Connection
     * @param schema     模式
     * @param tableName  表名
     */
    public static List<MetaColumn> getColumns(Connection connection, String schema, String tableName) throws SQLException {
        List<MetaColumn> resultList = new ArrayList<>();
        DatabaseMetaData meteData = connection.getMetaData();
        ResultSet rs;
        String databaseProductName = meteData.getDatabaseProductName();
        if (MYSQL_DIALECT.equals(databaseProductName)) {
            rs = meteData.getColumns(schema, null, tableName, null);
        } else {
            rs = meteData.getColumns(null, schema, tableName, null);
        }
        List<MetaPrimaryKey> primaryKeys = getPrimaryKeys(connection, schema, tableName);
        while (rs.next()) {
            MetaColumn metaColumn = new MetaColumn();
            metaColumn.setTableSchema(MYSQL_DIALECT.equals(meteData.getDatabaseProductName()) ?
                    rs.getString("TABLE_CAT") : rs.getString("TABLE_SCHEM"));
            metaColumn.setTableName(rs.getString("TABLE_NAME"));
            metaColumn.setColumnName(rs.getString("COLUMN_NAME"));
            metaColumn.setTypeName(rs.getString("TYPE_NAME"));
            metaColumn.setColumnSize(rs.getString("COLUMN_SIZE"));
            metaColumn.setRemarks(rs.getString("REMARKS"));
            metaColumn.setOrdinalPosition(rs.getString("ORDINAL_POSITION"));

            List<MetaPrimaryKey> filterPkColumns = primaryKeys.stream().filter(o -> o.getColumnName() != null
                    && o.getColumnName().equals(metaColumn.getColumnName())).collect(Collectors.toList());

            //处理当前列是否为主键
//            if (metaPrimaryKey.getColumnName() != null && metaPrimaryKey.getColumnName().equals(metaColumn.getColumnName())) {
            if (!filterPkColumns.isEmpty()) {
                metaColumn.setPkFlag(PrimaryKeyFlagTypeEnum.TRUE.initValue());
            } else {
                metaColumn.setPkFlag(PrimaryKeyFlagTypeEnum.FALSE.initValue());
            }
            resultList.add(metaColumn);
        }
        return resultList;
    }

    /**
     * 获取主键
     *
     * @param connection java.sql.Connection
     * @param schema     模式
     * @param tableName  表名
     */
    public static List<MetaPrimaryKey> getPrimaryKeys(Connection connection, String schema, String tableName) throws SQLException {
        List<MetaPrimaryKey> metaPrimaryKeys = new ArrayList<>();
        DatabaseMetaData meteData = connection.getMetaData();
        ResultSet rs;
        String databaseProductName = meteData.getDatabaseProductName();
        if (MYSQL_DIALECT.equals(databaseProductName)) {
            rs = meteData.getPrimaryKeys(schema, null, tableName);
        } else {
            rs = meteData.getPrimaryKeys(null, schema, tableName);
        }

        while (rs.next()) {
            MetaPrimaryKey metaPrimaryKey = new MetaPrimaryKey();
            metaPrimaryKey.setTableSchema(MYSQL_DIALECT.equals(meteData.getDatabaseProductName()) ?
                    rs.getString("TABLE_CAT") : rs.getString("TABLE_SCHEM"));
            metaPrimaryKey.setTableName(rs.getString("TABLE_NAME"));
            metaPrimaryKey.setColumnName(rs.getString("COLUMN_NAME"));
            metaPrimaryKey.setPkName(rs.getString("PK_NAME"));
            metaPrimaryKeys.add(metaPrimaryKey);
        }
        return metaPrimaryKeys;
    }

    /**
     * 主键标识
     */
    enum PrimaryKeyFlagTypeEnum {
        /**
         * 是主键
         */
        TRUE(1),
        /**
         * 否
         */
        FALSE(0);

        private int flag;

        PrimaryKeyFlagTypeEnum(int flag) {
            this.flag = flag;
        }

        public int initValue() {
            return flag;
        }
    }

    /**
     * Table Type 标识
     */
    public enum TableTypeEnum {
        TABLE("TABLE"),
        VIEW("VIEW"),
        SYSTEM_TABLE("SYSTEM TABLE"),
        GLOBAL_TEMPORARY("GLOBAL TEMPORARY"),
        LOCAL_TEMPORARY("LOCAL TEMPORARY"),
        ALIAS("ALIAS"),
        SYNONYM("SYNONYM");

        private String name;

        TableTypeEnum(String name) {
            this.name = name;
        }

        public String initValue() {
            return name;
        }
    }

    public static List<MetaPrimaryKey> getPrimaryKeysMap(Connection connection, String schema, List<String> tableNameList) throws SQLException {
        List<MetaPrimaryKey> metaPrimaryKeys = new ArrayList<>();
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            String databaseProductName = metaData.getDatabaseProductName();

            for (String tableName : tableNameList) {
                ResultSet pkResultSet;
                if (MYSQL_DIALECT.equals(databaseProductName)) {
                    pkResultSet = metaData.getPrimaryKeys(schema, null, tableName);
                } else {
                    pkResultSet = metaData.getPrimaryKeys(null, schema, tableName);
                }
                while (pkResultSet.next()) {
                    MetaPrimaryKey metaPrimaryKey = new MetaPrimaryKey();
                    metaPrimaryKey.setTableSchema(MYSQL_DIALECT.equals(metaData.getDatabaseProductName()) ?
                            pkResultSet.getString("TABLE_CAT") : pkResultSet.getString("TABLE_SCHEM"));
                    metaPrimaryKey.setTableName(pkResultSet.getString("TABLE_NAME"));
                    metaPrimaryKey.setColumnName(pkResultSet.getString("COLUMN_NAME"));
                    metaPrimaryKey.setPkName(pkResultSet.getString("PK_NAME"));
                    metaPrimaryKeys.add(metaPrimaryKey);
                }
                pkResultSet.close();
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
        return metaPrimaryKeys;
    }

    public static List<MetaColumn> getColumnsMap(Connection connection, String schema, List<MetaPrimaryKey> primaryKeys, List<String> tableNameList) throws SQLException {
        List<MetaColumn> resultList = new ArrayList<>();
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            String databaseProductName = metaData.getDatabaseProductName();

            for (String tableName : tableNameList) {
                ResultSet rs;
                if (MYSQL_DIALECT.equals(databaseProductName)) {
                    rs = metaData.getColumns(schema, null, tableName, null);
                } else {
                    rs = metaData.getColumns(null, schema, tableName, null);
                }
                while (rs.next()) {
                    MetaColumn metaColumn = new MetaColumn();
                    metaColumn.setTableSchema(MYSQL_DIALECT.equals(metaData.getDatabaseProductName()) ?
                            rs.getString("TABLE_CAT") : rs.getString("TABLE_SCHEM"));
                    metaColumn.setTableName(rs.getString("TABLE_NAME"));
                    metaColumn.setColumnName(rs.getString("COLUMN_NAME"));
                    metaColumn.setTypeName(rs.getString("TYPE_NAME"));
                    metaColumn.setColumnSize(rs.getString("COLUMN_SIZE"));
                    metaColumn.setRemarks(rs.getString("REMARKS"));
                    metaColumn.setOrdinalPosition(rs.getString("ORDINAL_POSITION"));

                    List<MetaPrimaryKey> filterPkColumns = primaryKeys.stream().filter(o -> o.getColumnName() != null && o.getTableName() != null
                            && o.getTableName().equals(tableName) && o.getColumnName().equals(metaColumn.getColumnName())).collect(Collectors.toList());

                    //处理当前列是否为主键
                    if (!filterPkColumns.isEmpty()) {
                        metaColumn.setPkFlag(PrimaryKeyFlagTypeEnum.TRUE.initValue());
                    } else {
                        metaColumn.setPkFlag(PrimaryKeyFlagTypeEnum.FALSE.initValue());
                    }
                    resultList.add(metaColumn);
                }
                rs.close();
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
        return resultList;
    }


}
